Skip to main content

Filtering

Basic Filtering​

Use WHERE clause to filter messages. Enclose in single quotes the filtering value - see attached example.

Using WHERE for filtering
#Shell format

==> SELECT * FROM tickquerydemo WHERE symbol = 'XBANK'

>deltix.timebase.api.messages.BestBidOfferMessage,TIMESTAMP,SYMBOL,TYPE,offerPrice,offerSize,bidPrice,bidSize
0,2011-10-17 17:21:40,XBANK,EQUITY,301.75,40000.0,301.25,800.0
>deltix.timebase.api.messages.TradeMessage,TIMESTAMP,SYMBOL,TYPE,price,size
1,2011-10-17 17:21:41,XBANK,EQUITY,301.25,800.0
>deltix.timebase.api.messages.BestBidOfferMessage,TIMESTAMP,SYMBOL,TYPE,offerPrice,offerSize,bidPrice,bidSize
2,2011-10-17 17:21:42,XBANK,EQUITY,301.5,60000.0,298.5,800.0
3,2011-10-17 17:21:43,XBANK,EQUITY,299.5,40000.0,295.0,300.0

Filtering by Time​

To filter messages by timestamp, you need to include relational conditions comparing the special field timestamp with, typically, a date literal.

Filter by time
#Shell format

==> SELECT * FROM tickquerydemo WHERE timestamp > '2011-10-17 17:21:40'd

>deltix.timebase.api.messages.TradeMessage,TIMESTAMP,SYMBOL,TYPE,price,size
0,2011-10-17 17:21:41,XBANK,EQUITY,301.25,800.0
>deltix.timebase.api.messages.BestBidOfferMessage,TIMESTAMP,SYMBOL,TYPE,offerPrice,offerSize,bidPrice,bidSize
1,2011-10-17 17:21:42,XBANK,EQUITY,301.5,60000.0,298.5,800.0
2,2011-10-17 17:21:43,GREATCO,EQUITY,45.0,100.0,43.0,400.0
3,2011-10-17 17:21:43,XBANK,EQUITY,299.5,40000.0,295.0,300.0
>deltix.timebase.api.messages.TradeMessage,TIMESTAMP,SYMBOL,TYPE,price,size
4,2011-10-17 17:21:44,GREATCO,EQUITY,44.0,100.0

==> SELECT * FROM tickquerydemo WHERE timestamp BETWEEN '2011-10-17 17:21:41'd AND '2011-10-17 17:21:42'd

>deltix.timebase.api.messages.TradeMessage,TIMESTAMP,SYMBOL,TYPE,price,size
0,2011-10-17 17:21:41,XBANK,EQUITY,301.25,800.0
>deltix.timebase.api.messages.BestBidOfferMessage,TIMESTAMP,SYMBOL,TYPE,offerPrice,offerSize,bidPrice,bidSize
1,2011-10-17 17:21:42,XBANK,EQUITY,301.5,60000.0,298.5,800.0

TimeBase will perform timestamp filtering against literals very efficiently, using the internal time index. For example, the above query does not cause TimeBase to read all data from the beginning of the stream, while testing each message against the filter. Instead, TimeBase will use the internal time index to start reading stream at the exact point in the stream where messages with the first timestamp, larger than 2011-10-17 17:21:40, begin.

Meet the below conditions in order for TimeBase to recognize your intent and perform this optimization:

  • Directly compare timestamp with a date literal (or parameter) using a relational operator <, >, <=, >= or between. See Constants to learn more about available time constants formats.
  • There must be either the only condition of the select statement, or one of several connected by the conjunction operator and conditions.
tip

Unless you specify a timezone all timestamp literals assume GMT timestamp:

Filter selection by the time zone
SELECT * FROM gaps WHERE timestamp > '2015-03-04 10:55:00 America/New_York'd
  • It does not matter whether timestamp is on the left or right side of the relational operator.
  • You can have any number of conditions connected by and and intermixed with any number of unrelated conditions, as long as time conditions are at the top level.

As mentioned above, you can also use the between expression. Remember that between is an inclusive condition:

Select from the time range using BETWEEN/AND
SELECT * FROM tickquerydemo WHERE timestamp BETWEEN '2011-10-17 17:21:41'd AND '2011-10-17 17:21:42'd

Filtering by Object Type​

Use THIS keyword to filter queries by object type.

Select from a specific object
#returns all records from TradeMessage object type that meet the condition
SELECT * FROM tickquerydemo WHERE price > 300 AND THIS IS deltix.timebase.api.messages.TradeMessage
Please Note
  • this means current message in this case
  • is <class_name> creates an object type check
  • In type names you can use the dot character without enclosing the entire identifier in quotes.
  • The capitalization of the type name is irrelevant, because QQL performs case-insensitive matching of identifiers, unless existing identifiers differ in case only.

Nullability​

Any data field in QQL may be declared as nullable, regardless of its data type. If so declared, it may contain the special out-of-band value of NULL, which basically means "no data". Additionally, NULL values are generated by queries in special cases. A NULL value is formatted as an underscore character: "_", so it can be differentiated from an empty string. Unlike ORACLE, an empty string value is distinctly different from a NULL value.

Array elements can as well be nullable and not nullable.

Skip NULL values
#filter out NULL values
SELECT  offerPrice AS 'price' FROM tickquerydemo
WHERE symbol = 'GREATCO' AND offerPrice IS NOT NULL